﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using NyBio.Model;

namespace NyBio.DB
{
    public class RoomDB
    {
        private Connection connection;


        public RoomDB()
        {
            connection = new Connection();
        }

        public List<Room> getRooms()
        {
            return where("");
        }

        public Room getRoomById(int id)
        {
            return singleWhere("xpRoom_id = " + id);
        }

        private Room singleWhere(String wClause)
        {
            Room room = null;

            string query = "SELECT * FROM XpRoom WHERE " + wClause;
            connection.Open();
            SqlDataReader reader = connection.Read(query);

            if (reader.Read())
            {
                room = new Room();
                room._id = reader.GetInt32(0);
                room._name = reader.GetString(1);
                room._numberOfRows = reader.GetInt32(2);
                room._numberOfColumns = reader.GetInt32(3);
            }

            reader.Close();
            connection.Close();

            return room;
        }

        private List<Room> where(String wClause)
        {
            List<Room> rooms = new List<Room>();

            string query;
            if (!wClause.Equals(""))
                query = "SELECT * FROM XpRoom WHERE " + wClause;
            else
                query = "SELECT * FROM XpRoom";
            connection.Open();
            SqlDataReader reader = connection.Read(query);

            while (reader.Read())
            {
                Room room = new Room();
                room._id = reader.GetInt32(0);
                room._name = reader.GetString(1);
                room._numberOfRows = reader.GetInt32(2);
                room._numberOfColumns = reader.GetInt32(3);
                rooms.Add(room);
            }

            reader.Close();
            connection.Close();

            return rooms;
        }
        public void insertRoom(Room room)
        {
            string query = @"INSERT INTO XpRoom (name, numberOfRows, numberOfColumns) " +
                "VALUES ( " + room._name + ", " + room._numberOfRows
                + ", " + room._numberOfColumns + ")";
            connection.Open();
            connection.Execute(query);
            connection.Close();
        }

        public Room getLatestRoom()
        {
            return where("order by 1 desc")[0];
        }

        public List<Room> getRoomSearch(string name)
        {
            return where("WHERE name LIKE '" + name + "'");
        }
    }
}